package ssau.knyazev.db;


import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.jdom.*;

import ssau.knyazev.cafe.gui.CafeApplication;
import ssau.knyazev.common.*;
import ssau.knyazev.db.common.FrameworkSettings;
import ssau.knyazev.db.core.ConnectionHelper;


import java.sql.*;


public class DBManager {

	protected String userID = null;
	protected String userName = null;
	protected String userRole = null; 
	protected String state = "LoginPanel";
	protected static final DBManager inst = new DBManager();
	
	protected DBManager(){ }
	
	public static DBManager getInst(){
		return inst;
	}
	
	public void enter(String userID, String userName, String userRole){
		this.userID = userID;
		this.userName = userName;
		this.userRole = userRole;
	}
	
	
	/**
	 * Error!!!
	 */
	public void setState(String state){
		this.state = state;
		CafeApplication.getInst().setPanel(state);
	}
	
	public String getState(){
		return state;
	}
	
	public String getUserName(){
		return userName;
	}
	
	public String getUserID(){
		return userID;
	}
	
	public String getUserRole(){
		return userRole;
	}
	
	public Document writeQueryResultsToXML(String query) throws UniException {
		Document res = null;
		Element root = new Element("table");
		res = new Document(root);
		Connection con = ConnectionHelper.getConnection();
		Statement state = null;
		try {
			state = con.createStatement();
			ResultSet rs = state.executeQuery(query);
			root = parseResultSet(root, rs);
		} catch (SQLException e) {
			throw new UniException("Could not perform query:\n" + query, e);
		} finally {
			ConnectionHelper.closeConnection(con, state);
		}
		return res;
	}
	
	public Document writeQueryResultsToXML(String query, Map<String, String> params) throws UniException {
		Document res = null;
		Element root = new Element("table");
		res = new Document(root);
		Connection con = ConnectionHelper.getConnection();
		PreparedStatement state = null;
		try {
			state = con.prepareStatement(query);
			String[] keys = QueryParser.parseSelectQuery(query);
			int i = 1;
			for (String key: keys){
				state.setString(i, params.get(key.toUpperCase()));
				System.out.println(key + " --- " + params.get(key.toUpperCase()));
				i++;
			}
			ResultSet rs = state.executeQuery();
			System.out.println("rs = " + rs);
			root = parseResultSet(root, rs);
		} catch (SQLException e) {
			throw new UniException("Could not perform query:\n" + query, e);
		} finally {
			ConnectionHelper.closeConnection(con, state);
		}
		return res;
	}
	
	public Document writeQueryResultsToXML(String query, List<String> params) throws UniException {
		Document res = null;
		Element root = new Element("table");
		res = new Document(root);
		Connection con = ConnectionHelper.getConnection();
		PreparedStatement state = null;
		try {
			state = con.prepareStatement(query);
			int i = 1;
			for (String param: params){
				state.setString(i, param);
				i++;
			}
			ResultSet rs = state.executeQuery();
			System.out.println(rs);
			root = parseResultSet(root, rs);
		} catch (SQLException e) {
			throw new UniException("Could not perform query:\n" + query, e);
		} finally {
			ConnectionHelper.closeConnection(con, state);
		}
		return res;
	}
	
	private Element parseResultSet(Element root, ResultSet rs) throws SQLException{
		if (rs == null)
			return null;
		ResultSetMetaData meta = rs.getMetaData();
		int count = meta.getColumnCount();
		Element emeta = new Element(FrameworkSettings.TABLE_META);
		root.addContent(emeta);
		for (int i = 0; i < count; i++) {
			Element e = new Element(FrameworkSettings.TABLE_ROW);
			e.setText(meta.getColumnName(i + 1));
			emeta.addContent(e);
			System.out.println(meta.getColumnName(i + 1));
		}

		Element edata = new Element(FrameworkSettings.TABLE_DATA);
		while (rs.next()) {
			Element erecord = new Element(FrameworkSettings.TABLE_RECORD);
			for (int i = 1; i <= count; i++) {
				Element erow = new Element(FrameworkSettings.TABLE_ROW);
				erow.setText(rs.getString(i));
				erecord.addContent(erow);
			}
			edata.addContent(erecord);
		}
		root.addContent(edata);
		return root;
	}

	
	public List<ListValue> getComboBoxList(String query) throws UniException{
		List<ListValue> res = new LinkedList<ListValue>();
		Connection con = ConnectionHelper.getConnection();
		Statement state = null;
		try {
			state = con.createStatement();
			ResultSet rs = state.executeQuery(query);
			System.out.println(query);
			if (rs == null)
				throw new Exception("ResultSet is empty");
			while (rs.next()) {
				res.add(new ListValue(rs.getString(FrameworkSettings.LIST_KEY), rs.getString(FrameworkSettings.LIST_VALUE))); 
			}
		} catch (SQLException e){
			throw new UniException("Could not execute query:\n" + query, e);
		} catch (Exception e){
			e.printStackTrace();
		} finally {
			ConnectionHelper.closeConnection(con, state);
		}
		return res;
	}
	
	public void removeRecord(String tableName, Map<String, String> params) throws UniException{
		/*Element table = findTableWithName(tableName);
		String query = getQuery(tableName, FrameworkSettings.MODEL_DELETE_DATA_QUERY);
		System.out.println(tableName + "remove record");
		System.out.println(query);
		System.out.println(params);
		Map<String, String> origNames = QueryParser
		.parseSelectQueryForColumns(DBManager.getInst()
				.getQuery(tableName, FrameworkSettings.MODEL_ALL_DATA_QUERY), true);
		List<String> keys = QueryParser.parseDeleteQuery(query);
		for(String x: keys){
			params.put(x, params.get(origNames.get(x)));
		}
		execute(query, params, keys);*/
	}
	
	public void removeRecord(Map<String, String> params) throws UniException{
		String query = params.get("query");
		List<String> keys = QueryParser.parseDeleteQuery(query);
		execute(query, params, keys);
	}
	
	public void addRecord(String tableName, Map<String, String> params) throws UniException{
		/*Element table = findTableWithName(tableName);
		String query = getQuery(tableName, FrameworkSettings.MODEL_INSERT_DATA_QUERY);
		System.out.println(tableName + "add record");
		System.out.println(query);
		System.out.println(params);
		List<String> keys = QueryParser.parseInsertQuery(query);
		execute(query, params, keys);*/
	}
	
	public void changeRecord(String tableName, Map<String, String> params, Map<String, String> oldParams) throws UniException{
		/*Element table = findTableWithName(tableName);
		String query = getQuery(tableName, FrameworkSettings.MODEL_UPDATE_DATA_QUERY);
		System.out.println(tableName + "change record");
		System.out.println(query);
		System.out.println(params);
		execute(table, query, params, oldParams);*/
	}
	
	private void execute(String query, Map<String, String> params, List<String> keys) throws UniException{
		Connection con = null;
		PreparedStatement state = null;
		System.out.println("ApplicationManager::execute START");
		System.out.println("PARAMS = " + params);
		try{
			con = ConnectionHelper.getConnection();
			state = con.prepareStatement(query);
			for (int i = 1; i <= keys.size(); i++){
				state.setString(i, params.get(keys.get(i-1)));
			}
			state.executeUpdate();
		} catch (SQLException e){
			throw new UniException("Could not execute query:\n" + query, e);
		} finally {
			ConnectionHelper.closeConnection(con, state);
			System.out.println("ApplicationManager::execute END");
		}
	}
	
	public boolean execute(String query, List<String> params) throws UniException{
		Connection con = null;
		PreparedStatement state = null;
		try{
			con = ConnectionHelper.getConnection();
			state = con.prepareStatement(query);
			int i = 1;
			for (String parameter: params){
				state.setString(i, parameter);
				i++;
			}
			state.executeUpdate();
			return true;
		} catch (SQLException e){
			throw new UniException("Could not execute query:\n" + query, e);
		} finally {
			ConnectionHelper.closeConnection(con, state);
		}
	}
	
	public boolean select(String query, List<String> params) throws UniException{
		Connection con = null;
		PreparedStatement state = null;
		try{
			state = con.prepareStatement(query);
			int i = 1;
			for (String param: params){
				state.setString(i, param);
				i++;
			}
			ResultSet rs = state.executeQuery();
			return rs.next();
		} catch (SQLException e){
			throw new UniException("Could not execute query:\n" + query, e);
		} finally {
			ConnectionHelper.closeConnection(con, state);
		}
	}
	
	@SuppressWarnings("unchecked")
	private void execute(Element table, String query, Map<String, String> params, Map<String, String> oldParams) throws UniException{
		Connection con = null;
		PreparedStatement state = null;
		System.out.println("ApplicationManager::execute START");
		System.out.println("PARAMS = " + params);
		try{
			con = ConnectionHelper.getConnection();
			state = con.prepareStatement(query);
			List<Element> fields = table.getChild(FrameworkSettings.MODEL_TABLE_STRUCTURE).getChildren(FrameworkSettings.MODEL_FIELD);
			int i = 1;
			for (Element field: fields){
				String name = field.getChild(FrameworkSettings.MODEL_FIELD_NAME).getText();
				state.setString(i, params.get(name));
				System.out.println("name = " + name + " value = " + params.get(name));
				i++;
			}
			for (Element field: fields){
				String name = field.getChild(FrameworkSettings.MODEL_FIELD_NAME).getText();
				state.setString(i, oldParams.get(name));
				System.out.println("name = " + name + " value = " + oldParams.get(name));
				i++;
			}
			state.executeUpdate();
		} catch (SQLException e){
			throw new UniException("Could not execute query:\n" + query, e);
		} finally {
			ConnectionHelper.closeConnection(con, state);
			System.out.println("ApplicationManager::execute END");
		}
	}
	
	@SuppressWarnings("unchecked")

	

	
	public void executeAutoInsert(String tableName) throws UniException{
		/*Element table = findTableWithName(tableName);
		String query = table.getChildText(FrameworkSettings.MODEL_INSERT_DATA_QUERY);
		Map<String, String> params = new HashMap<String, String>();
		List parameters = table.getChild(FrameworkSettings.MODEL_INSERT_DATA_QUERY).getChildren("parameter");
		List<String> keys = new LinkedList<String>();
		for (Object o: parameters){
			Element parameter = (Element) o;
			String text = parameter.getText();
			System.out.println(text);
			keys.add(text);
			if (text.trim().equals("user_id")){
				params.put("user_id", userID);
			}
		}
		execute(query, params, keys);
		*/
	}

	
}
